/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package LaporanTransaksi;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import tools.koneksi;

/**
 *
 * @author w7
 */
public class LaporanTransaksiKontrol {

    Connection conn;
    private static LaporanTransaksiKontrol laporanKontrol;

    public LaporanTransaksiKontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static LaporanTransaksiKontrol getKoneksiLaporan() throws SQLException {
        koneksi k = new koneksi();
        if (laporanKontrol == null) {
            laporanKontrol = new LaporanTransaksiKontrol(koneksi.getDBConnection());
        }
        return laporanKontrol;

    }

//    public List<LaporanTransaksi> buatDataLaporan(String kodeTransaksi) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        try {
//            conn.setAutoCommit(false);
//            statement = conn.prepareStatement("select * from TRANSAKSI where kode_rekening ='" + kodeTransaksi + "'");
//            result = statement.executeQuery();
//
//            //List<kategori> k = new ArrayList<kategori>();
//            LaporanTransaksi lap = null;
//            while (result.next()) {
//                lap = new LaporanTransaksi();
////
//                String id = result.getString("id_anggota");
//                Date tanggal = result.getDate("tanggal");
//                String noInvoice = result.getString("no_invoice");
//                Double pokokPinjaman = result.getDouble("pokok_pinjaman");
//                Double bunga = result.getDouble("bunga");
//                String keterangan = result.getString("keterangan");
//                Double danaMasuk = result.getDouble("dana_masuk");
//                Double danaKeluar = result.getDouble("dana_keluar");
//                Double saldo = result.getDouble("saldo");
//                lap.setID_Anggota(id);
//                lap.setTanggal(tanggal);
//                lap.setNoInvoice(noInvoice);
//                lap.setPokokPinjaman(pokokPinjaman);
//                lap.setBunga(bunga);
//                lap.setKeterangan(keterangan);
//                lap.setDanaMasuk(danaMasuk);
//                lap.setDanaKeluar(danaKeluar);
//                lap.setSaldo(saldo);
////                k.addRow(lap);
//                k.add(lap);
//
//            }
//            conn.commit();
//        } catch (Exception e) {
//        }
//
//        return k;
//    }


     public List<LaporanTransaksi> getAllLaporan(String tglAwal, String tglAkhir) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
         List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
try{

        conn.setAutoCommit(false);
        statement = conn.prepareStatement("select NO_INVOICE, id_anggota, id_unit, TANGGAL, KODE_REKENING, KETERANGAN, jumlah_dana_masuk,"
                + " JUMLAH_PERMINTAAN_DANA from TRANSAKSI left JOIN PENGELUARAN1 USING(NO_INVOICE) left JOIN PEMASUKAN1 USING(NO_INVOICE)"
                + " WHERE TANGGAL BETWEEN '"+tglAwal+"' and '"+tglAkhir+"' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//        statement.setString(1, tglAwal);
//        statement.setString(2, tglAkhir);
        result = statement.executeQuery();

        Double saldoSesudah = 0.00;
        while (result.next()) {
            LaporanTransaksi bar = new LaporanTransaksi();
            bar.setNoInvoice(result.getString(1));
            bar.setID_Anggota(result.getString(2));
            bar.setID_Unit(result.getString(3));
            bar.setTanggal(result.getDate(4));
            bar.setKodeRekening(result.getString(5));
            bar.setKeterangan(result.getString(6));
            bar.setDanaMasuk(result.getDouble(7));
            bar.setDanaKeluar(result.getDouble(8));
            Double saldoSebelum = result.getDouble(7) - result.getDouble(8);
            saldoSesudah = saldoSesudah + saldoSebelum;
            bar.setSaldo(saldoSesudah);
            k.add(bar);
            saldoSebelum = 0.00;
        }
        conn.commit();
         }catch (SQLException ex) {
            Logger.getLogger(LaporanTransaksiKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
         return k;
    }


//     public List<LaporanTransaksi> cariKodeP2(String P2) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//
//        conn.setAutoCommit(false);
//        statement = conn.prepareStatement(" select TANGGAL, NO_INVOICE, ID_ANGGOTA, KETERANGAN, JUMLAH_DANA_MASUK, POKOK_PINJAMAN, BUNGA from PEMASUKAN "
//                + " where KODE_REKENING = 'P2' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//      //   statement.setString(1,P1);
//        result = statement.executeQuery();
//
//        Double saldoSesudah = 0.00;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        while (result.next()) {
//            LaporanTransaksi bar = new LaporanTransaksi();
//            bar.setTanggal(result.getDate(1));
//            bar.setNoInvoice(result.getString(2));
//            bar.setID_Anggota(result.getString(3));
//            bar.setKeterangan(result.getString(4));
//            bar.setDanaMasuk(result.getDouble(5));
//            bar.setDanaKeluar(result.getDouble(6));
//            Double saldoSebelum = result.getDouble(5) - result.getDouble(6);
//            saldoSesudah = saldoSesudah + saldoSebelum;
//            bar.setSaldo(saldoSesudah);
//            bar.setBunga(result.getDouble(7));
//            k.add(bar);
//            saldoSebelum = 0.00;
//        }
//        conn.commit();
//        return k;
//    }
//
//
//     public List<LaporanTransaksi> cariKodeP3(String P3) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//
//        conn.setAutoCommit(false);
//        statement = conn.prepareStatement(" select TANGGAL, NO_INVOICE, ID_ANGGOTA, KETERANGAN, JUMLAH_DANA_MASUK, POKOK_PINJAMAN, bunga from PEMASUKAN "
//                + " where KODE_REKENING = 'P3' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//      //   statement.setString(1,P1);
//        result = statement.executeQuery();
//
//        Double saldoSesudah = 0.00;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        while (result.next()) {
//            LaporanTransaksi bar = new LaporanTransaksi();
//            bar.setTanggal(result.getDate(1));
//            bar.setNoInvoice(result.getString(2));
//            bar.setID_Anggota(result.getString(3));
//            bar.setKeterangan(result.getString(4));
//            bar.setDanaMasuk(result.getDouble(5));
//            bar.setDanaKeluar(result.getDouble(6));
//            Double saldoSebelum = result.getDouble(5) - result.getDouble(6);
//            saldoSesudah = saldoSesudah + saldoSebelum;
//            bar.setSaldo(saldoSesudah);
//            bar.setBunga(result.getDouble(7));
//            k.add(bar);
//            saldoSebelum = 0.00;
//        }
//        conn.commit();
//        return k;
//    }
//
//     public List<LaporanTransaksi> cariKodeP4(String P4) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//
//        conn.setAutoCommit(false);
//        statement = conn.prepareStatement(" select TANGGAL, NO_INVOICE, ID_ANGGOTA, KETERANGAN, JUMLAH_DANA_MASUK, POKOK_PINJAMAN, bunga from PEMASUKAN "
//                + " where KODE_REKENING = 'P4' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//      //   statement.setString(1,P1);
//        result = statement.executeQuery();
//
//        Double saldoSesudah = 0.00;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        while (result.next()) {
//            LaporanTransaksi bar = new LaporanTransaksi();
//            bar.setTanggal(result.getDate(1));
//            bar.setNoInvoice(result.getString(2));
//            bar.setID_Anggota(result.getString(3));
//            bar.setKeterangan(result.getString(4));
//            bar.setDanaMasuk(result.getDouble(5));
//            bar.setDanaKeluar(result.getDouble(6));
//            Double saldoSebelum = result.getDouble(5) - result.getDouble(6);
//            saldoSesudah = saldoSesudah + saldoSebelum;
//            bar.setSaldo(saldoSesudah);
//            bar.setBunga(result.getDouble(7));
//            k.add(bar);
//            saldoSebelum = 0.00;
//        }
//        conn.commit();
//        return k;
//    }
//
//     public List<LaporanTransaksi> cariKodeP5(String P5) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//
//        conn.setAutoCommit(false);
//        statement = conn.prepareStatement(" select TANGGAL, NO_INVOICE, ID_ANGGOTA, KETERANGAN, JUMLAH_DANA_MASUK, POKOK_PINJAMAN, bunga from PEMASUKAN "
//                + " where KODE_REKENING = 'P5' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//      //   statement.setString(1,P1);
//        result = statement.executeQuery();
//
//        Double saldoSesudah = 0.00;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        while (result.next()) {
//            LaporanTransaksi bar = new LaporanTransaksi();
//            bar.setTanggal(result.getDate(1));
//            bar.setNoInvoice(result.getString(2));
//            bar.setID_Anggota(result.getString(3));
//            bar.setKeterangan(result.getString(4));
//            bar.setDanaMasuk(result.getDouble(5));
//            bar.setDanaKeluar(result.getDouble(6));
//            Double saldoSebelum = result.getDouble(5) - result.getDouble(6);
//            saldoSesudah = saldoSesudah + saldoSebelum;
//            bar.setSaldo(saldoSesudah);
//            bar.setBunga(result.getDouble(7));
//            k.add(bar);
//            saldoSebelum = 0.00;
//        }
//        conn.commit();
//        return k;
//    }
//
//     public List<LaporanTransaksi> cariKodeK1(String K1) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//
//        conn.setAutoCommit(false);
//        statement = conn.prepareStatement(" select TANGGAL, NO_INVOICE, ID_UNIT, KETERANGAN, JUMLAH_PERMINTAAN_DANA from PENGELUARAN "
//                + " where KODE_REKENING = 'K1' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//      //   statement.setString(1,P1);
//        result = statement.executeQuery();
//
//        Double saldoSesudah = 0.00;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        while (result.next()) {
//            LaporanTransaksi bar = new LaporanTransaksi();
//            bar.setTanggal(result.getDate(1));
//            bar.setNoInvoice(result.getString(2));
//            bar.setID_Anggota(result.getString(3));
//            bar.setKeterangan(result.getString(4));
//
//            bar.setDanaMasuk(result.getDouble(5));
//            bar.setDanaKeluar(result.getDouble(6));
//            Double saldoSebelum = result.getDouble(5) - result.getDouble(6);
//            saldoSesudah = saldoSesudah + saldoSebelum;
//            bar.setSaldo(saldoSesudah);
//        //    bar.setTotalDanaSimpanan(result.getDouble(7));
//            k.add(bar);
//            saldoSebelum = 0.00;
//        }
//        conn.commit();
//        return k;
//    }
//     public List<LaporanTransaksi> cariKodeK2(String P5) throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//
//        conn.setAutoCommit(false);
//        statement = conn.prepareStatement(" select TANGGAL, NO_INVOICE, ID_ANGGOTA, KETERANGAN, JUMLAH_DANA_MASUK, POKOK_PINJAMAN, bunga from PEMASUKAN "
//                + " where KODE_REKENING = 'P5' order by no_INVOICE, ID_ANGGOTA, TANGGAL");
//      //   statement.setString(1,P1);
//        result = statement.executeQuery();
//
//        Double saldoSesudah = 0.00;
//        List<LaporanTransaksi> k = new ArrayList<LaporanTransaksi>();
//        while (result.next()) {
//            LaporanTransaksi bar = new LaporanTransaksi();
//            bar.setTanggal(result.getDate(1));
//            bar.setNoInvoice(result.getString(2));
//            bar.setID_Anggota(result.getString(3));
//            bar.setKeterangan(result.getString(4));
//            bar.setDanaMasuk(result.getDouble(5));
//            bar.setDanaKeluar(result.getDouble(6));
//            Double saldoSebelum = result.getDouble(5) - result.getDouble(6);
//            saldoSesudah = saldoSesudah + saldoSebelum;
//            bar.setSaldo(saldoSesudah);
//            bar.setBunga(result.getDouble(7));
//            k.add(bar);
//            saldoSebelum = 0.00;
//        }
//        conn.commit();
//        return k;
//    }

}
